﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OleDb;
using System.Data;

namespace Controllers
{
    public class ChoferController
    {
        public static List<Chofer> ObtenerChoferesTop5(string date_from, string date_to)
        {
            var list = new List<Chofer>();

            var sql = "SELECT top 5 Chofer_Nombre, Chofer_Apellido, Chofer_Dni, sum(Viaje_Importe) [Recaudacion] " +
                        "FROM Superacidos.Viaje " +
                        "JOIN Superacidos.Chofer on Chofer_Id = Viaje_ChoferId " +
                        "WHERE Viaje_fecha between '" + date_from + "' and '" + date_to + "' " +
                        "Group by Viaje_ChoferId, Chofer_Nombre, Chofer_Apellido, Chofer_Dni, Viaje_Importe " +
                        "Order By Recaudacion DESC";
            var reader = ConexionBaseDeDatos.GetData(sql, CommandType.Text);
            if (reader.HasRows)
            {
                while (reader.Read())
                {
                    list.Add(ObtenerObjetoEstadistico(reader));
                }
            }

            return list;
        }

        public static List<Chofer> ObtenerTodos(string where, int pagina, int cantidadRegistros)
        {
            var list = new List<Chofer>();

            var sql = "Select TOP " + cantidadRegistros + " * From Superacidos.Chofer";

            if (pagina > 1)
            {
                int limiteAnterior = (pagina - 1) * cantidadRegistros;


                var filtrospaginador = !string.IsNullOrEmpty(where)
                                       ? " Chofer_Id NOT IN " + "(Select TOP " + limiteAnterior +
                                         " Chofer_Id from Superacidos.Chofer WHERE " + where +
                                         " ORDER BY Chofer_Id) "
                                       : " Chofer_Id NOT IN " + "(Select TOP " + limiteAnterior +
                                         " Chofer_Id from Superacidos.Chofer ORDER BY Chofer_Id) ";

                if (!string.IsNullOrEmpty(where))
                {
                    where += " AND ";
                }
                where += filtrospaginador;
            }


            if (!string.IsNullOrEmpty(where))
            {
                sql += " WHERE " + where;
            }

            var reader = ConexionBaseDeDatos.GetData(sql, CommandType.Text);

            if (reader.HasRows)
            {
                while (reader.Read())
                {
                    list.Add(ObtenerObjeto(reader));
                }
            }

            return list;
        }

        public static List<Chofer> ObtenerTodos(string where)
        {
            var list = new List<Chofer>();
            var sql = "Select * From Superacidos.Chofer";

            if (!string.IsNullOrEmpty(where))
            {
                sql += " WHERE " + where;
            }

            var reader = ConexionBaseDeDatos.GetData(sql, CommandType.Text);

            if (reader.HasRows)
            {
                while (reader.Read())
                {
                    list.Add(ObtenerObjeto(reader));
                }
            }

            return list;
        }

        public static bool ExisteElTelefono(string telefono, int choferId)
        {

            var sql = "Select count(*) as Cantidad From Superacidos.Chofer WHERE Chofer_Telefono = " + telefono;
            if (choferId > 0)
            {
                sql += " AND Chofer_Id <> " + choferId;
            }

            var cantidad = 0;
            var reader = ConexionBaseDeDatos.GetData(sql, CommandType.Text);

            if (reader.HasRows)
            {
                reader.Read();
                if (reader["Cantidad"] != DBNull.Value)
                    cantidad = (int)reader["Cantidad"];

            }

            return cantidad > 0;
        }

        public static int ObtenerCantidad(string where)
        {
            var sql = "Select count(*) as Cantidad From Superacidos.Chofer";

            if (!string.IsNullOrEmpty(where))
            {
                sql += " WHERE " + where;
            }
            var cantidad = 0;
            var reader = ConexionBaseDeDatos.GetData(sql, CommandType.Text);

            if (reader.HasRows)
            {
                reader.Read();
                if (reader["Cantidad"] != DBNull.Value)
                    cantidad = (int)reader["Cantidad"];

            }

            return cantidad;
        }

        public static List<Chofer> ObtenerTodosHabilitados(string where)
        {
            if (!string.IsNullOrEmpty(where))
            {
                where += " AND ";
            }
            where += " Chofer_Habilitado = 1";

            return ObtenerTodos(where);
        }

        public static Chofer ObtenerUno(int choferId)
        {
            var reader = ConexionBaseDeDatos.GetData("Select * From Superacidos.Chofer Where Chofer_Id = " + choferId, CommandType.Text);

            return reader.HasRows ? ObtenerObjeto(reader) : null;
        }

        public static Chofer ObtenerObjeto(OleDbDataReader reader)
        {
            var chofer = new Chofer();

            if (reader["Chofer_Apellido"] != DBNull.Value)
                chofer.Chofer_Apellido = (string)reader["Chofer_Apellido"];
            if (reader["Chofer_Direccion"] != DBNull.Value)
                chofer.Chofer_Direccion = (string)reader["Chofer_Direccion"];
            if (reader["Chofer_Dni"] != DBNull.Value)
                chofer.Chofer_Dni = (decimal)reader["Chofer_Dni"];
            if (reader["Chofer_Fecha_Nac"] != DBNull.Value)
                chofer.Chofer_Fecha_Nac = (DateTime)reader["Chofer_Fecha_Nac"];
            if (reader["Chofer_Habilitado"] != DBNull.Value)
                chofer.Chofer_Habilitado = (bool)reader["Chofer_Habilitado"];
            if (reader["Chofer_Id"] != DBNull.Value)
                chofer.Chofer_Id = (int)reader["Chofer_Id"];
            if (reader["Chofer_Mail"] != DBNull.Value)
                chofer.Chofer_Mail = (string)reader["Chofer_Mail"];
            if (reader["Chofer_Nombre"] != DBNull.Value)
                chofer.Chofer_Nombre = (string)reader["Chofer_Nombre"];
            if (reader["Chofer_Telefono"] != DBNull.Value)
                chofer.Chofer_Telefono = (decimal)reader["Chofer_Telefono"];

            return chofer;
        }

        public static Chofer ObtenerObjetoEstadistico(OleDbDataReader reader)
        {
            var chofer = new Chofer();

            if (reader["Chofer_Apellido"] != DBNull.Value)
                chofer.Chofer_Apellido = (string)reader["Chofer_Apellido"];
            if (reader["Chofer_Dni"] != DBNull.Value)
                chofer.Chofer_Dni = (decimal)reader["Chofer_Dni"];
            if (reader["Chofer_Nombre"] != DBNull.Value)
                chofer.Chofer_Nombre = (string)reader["Chofer_Nombre"];
            if (reader["Recaudacion"] != DBNull.Value)
                chofer.Chofer_Recaudacion = (decimal)reader["Recaudacion"];

            return chofer;
        }

        public static void Agregar(Chofer chofer)
        {
            var sql = "INSERT INTO SUPERACIDOS.Chofer (Chofer_Nombre, Chofer_Apellido, Chofer_Dni, Chofer_Telefono, Chofer_Direccion," +
                      " Chofer_Mail, Chofer_Fecha_Nac, Chofer_Habilitado) " +
                      " VALUES ('{0}','{1}',{2},{3},'{4}','{5}','{6}',{7})";

            sql = string.Format(sql, chofer.Chofer_Nombre, chofer.Chofer_Apellido, chofer.Chofer_Dni, chofer.Chofer_Telefono,
                                     chofer.Chofer_Direccion,
                                     chofer.Chofer_Mail,
                                     chofer.Chofer_Fecha_Nac.ToString("yyyy-MM-dd"), 
                                     chofer.Chofer_Habilitado ? "1" : "0");


            ConexionBaseDeDatos.Guardar(sql, CommandType.Text);

        }

        public static void Modificar(Chofer chofer)
        {
            var sql = "update SUPERACIDOS.Chofer set Chofer_Nombre = '{0}' , Chofer_Apellido = '{1}', Chofer_Dni = {2}, Chofer_Telefono = {3}, " +
                      "Chofer_Direccion = '{4}', Chofer_Mail = '{5}', Chofer_Fecha_Nac = '{6}',Chofer_Habilitado = {7}" +
                      "where Chofer_Id = {8}";

            sql = string.Format(sql, chofer.Chofer_Nombre, chofer.Chofer_Apellido, chofer.Chofer_Dni, chofer.Chofer_Telefono,
                                     chofer.Chofer_Direccion,
                                     chofer.Chofer_Mail,
                                     chofer.Chofer_Fecha_Nac.ToString("yyyy-MM-dd"),
                                     chofer.Chofer_Habilitado ? "1" : "0",
                                     chofer.Chofer_Id);


            ConexionBaseDeDatos.Guardar(sql, CommandType.Text);

        }

        public static void Eliminar(int choferId)
        {
            var sql = "update SUPERACIDOS.Chofer set chofer_habilitado = 0 where Chofer_Id = " + choferId;
            ConexionBaseDeDatos.Guardar(sql, CommandType.Text);
        }

        

    }

}

